package com.joinway.platform.tools;

import static java.lang.System.out;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class TableExcelGenerator {

	private final static String[] TITLE = new String[]{"序号", "列名", "数据类型", "长度", "Nullable", "自增主键", "默认值", "注释"};
	private final static int TOTAL_COLUMNS = 8;
	private final static Map<Integer, String> types = new HashMap<>();
	
	static{
		types.put(Types.BIGINT, "bigint");
		types.put(Types.INTEGER, "int");
		types.put(Types.SMALLINT, "smallint");
		types.put(Types.TINYINT, "tinyint");
		types.put(Types.BIT, "bit");
		types.put(Types.FLOAT, "float");
		types.put(Types.DOUBLE, "double");
		types.put(Types.DECIMAL, "decimal");
		types.put(Types.CHAR, "char");
		types.put(Types.VARCHAR, "varchar");
		types.put(Types.BINARY, "binary");
		types.put(Types.VARBINARY, "varbinary");
		types.put(Types.BLOB, "blob");
		types.put(Types.DATE, "date");
		types.put(Types.TIME, "time");
		types.put(Types.TIMESTAMP, "datetime");
		types.put(Types.LONGVARCHAR, "text");
		types.put(Types.REAL, "float");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
//		types.put(Types, "");
	}
//	
	public static void generate(String user, String password, String ip, String dbname) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String connStr = "jdbc:mysql://" + ip + ":3306/" + dbname + "?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";
		out.println("connecting to " + connStr);
		
		Connection conn = DriverManager.getConnection(connStr, user, password);
		
		DatabaseMetaData meta = conn.getMetaData();
		
		ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE"});
		
		Workbook wb = new HSSFWorkbook();
		
		String file = "d:\\db.xls";
		
		CellStyle headStyle = getBorderStyle(wb);
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontHeightInPoints((short)11);
		headStyle.setFont(font);
		
		CellStyle style = getBorderStyle(wb);
		
		CellStyle style2 = getBorderStyle(wb);
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		
		while(rs.next()){
			int curRow = 1;
			
			String table = rs.getString(3);
//			if(!table.equals("vcu_user_wharehouse_rel")){
//				continue;
//			}
			out.println("generating " + table + " ...");
			
			Sheet sheet = wb.createSheet(table);
			Row titleRow = sheet.createRow(curRow++);
			
			// create header
			for(int i = 0; i < TOTAL_COLUMNS; i++){
				Cell cell = titleRow.createCell(i + 1);
				cell.setCellValue(TITLE[i]);

//				style.setFillBackgroundColor(HSSFColor.BLUE.index);
//				style.setFillForegroundColor(HSSFColor.WHITE.index);
//				style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				cell.setCellStyle(headStyle);
			}
			
			// set body
			ResultSet trs = meta.getColumns(null, null, table, null);

			int no = 1;
			while(trs.next()){
				Row row = sheet.createRow(curRow++);
				int column = 1;
				
				// 序号
				Cell cell = row.createCell(column++);
				cell.setCellValue(no++);
				cell.setCellStyle(style);
				
				// 列名
				cell = row.createCell(column++);
				cell.setCellValue(trs.getString("COLUMN_NAME"));
				cell.setCellStyle(style2);
				
				// 数据类型
				cell = row.createCell(column++);
				String type = types.get(trs.getInt("DATA_TYPE"));
				cell.setCellValue(type);
				cell.setCellStyle(style);
				
				// 长度
				cell = row.createCell(column++);
				String len = "";
				int size = trs.getInt("COLUMN_SIZE");
				if(type.endsWith("int")){
					size++;
				}
				
				Integer digits = trs.getInt("DECIMAL_DIGITS");
				if(digits == null || digits == 0){
					len = String.valueOf(size);
				}else{
					len = size + "," + digits;
				}
				
				if(type.equals("datetime") || type.equals("date") || type.equals("text") || type.equals("float")){
					len = "";
				}
				
				cell.setCellValue(len);
				cell.setCellStyle(style);
				
				// Nullable
				cell = row.createCell(column++);
				String nullable = trs.getString("IS_NULLABLE");
				cell.setCellValue(nullable.equalsIgnoreCase("no") ? "NOT NULL" : "");
				cell.setCellStyle(style);
				
				// 自增主键
				cell = row.createCell(column++);
				String auto = trs.getString("IS_AUTOINCREMENT");
				cell.setCellValue(auto.equalsIgnoreCase("yes") ? "Y" : "");
				cell.setCellStyle(style);
				
				// 默认值
				cell = row.createCell(column++);
				cell.setCellValue(trs.getString("COLUMN_DEF"));
				cell.setCellStyle(style);
				
				// 注释
				cell = row.createCell(column++);
				cell.setCellValue(trs.getString("REMARKS"));
				cell.setCellStyle(style2);
			}
			
			// 自动调整列宽
			for(int i = 1; i <= TOTAL_COLUMNS; i++){
				sheet.autoSizeColumn(i);
			}
			
//			break;
		}
		
		wb.write(new FileOutputStream(file));
		out.println("generated " + file);
	}
	
	private static CellStyle getBorderStyle(Workbook wb){
		CellStyle style = wb.createCellStyle();
		
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    
	    style.setTopBorderColor(HSSFColor.BLACK.index);
	    style.setBottomBorderColor(HSSFColor.BLACK.index);
	    style.setLeftBorderColor(HSSFColor.BLACK.index);
	    style.setRightBorderColor(HSSFColor.BLACK.index);
	    
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    
	    Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)11);
		style.setFont(font);
		
	    return style;
	}
	
//	
//	public static void generate2(String user, String password, String ip, String dbname) throws Exception {
//		Class.forName("com.mysql.jdbc.Driver");
//		Connection conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname + "?autoReconnect=true&useUnicode=true&characterEncoding=utf-8", user, password);
//		
//		DatabaseMetaData meta = conn.getMetaData();
//	
//		ResultSet rs = null;
//		rs = meta.getTables(null, null, null, new String[]{"TABLE"});
////		while(rs.next()){
////			for(int i = 1; i <= 11; i++){
////				out.println("[" + i + "]" + rs.getString(i));
////			}
////		}
////		int v = java.sql.Types.VARCHAR;
//		rs = meta.getColumns(null, null, "vcu_budget", null);
//		while(rs.next()){
////			String d = "";
////			if(rs.getInt("DATA_TYPE") == java.sql.Types.DECIMAL){
//////				d = String.valueOf(rs.getInt("DECIMAL_DIGITS"));
//////			}
////			String info = StringUtils.join(
////				rs.getString("COLUMN_NAME"), ","
////				, String.valueOf(rs.getInt("DATA_TYPE")), ","
////				, "(", String.valueOf(rs.getInt("COLUMN_SIZE")), ","
////				, String.valueOf(rs.getInt("DECIMAL_DIGITS"))
//////				, d
////				, ")", ","
////				, rs.getString("REMARKS"), ","
//////				, String.valueOf(rs.getInt("NULLABLE")), ","
////				, rs.getString("COLUMN_DEF"), ","
////				, rs.getString("IS_NULLABLE"), ","
////				, rs.getString("IS_AUTOINCREMENT"));
////			
////			out.println(info);
////		}
////	}
	
//	public static void getRsInfo(String user, String password, String ip, String dbname) throws Exception {  
//		Class.forName("com.mysql.jdbc.Driver");
//		Connection con = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbname + "?autoReconnect=true&useUnicode=true&characterEncoding=utf-8", user, password);
//		
//        PreparedStatement pst = con.prepareStatement("select * from user");  
//          
//        ResultSet rs = pst.executeQuery();  
//          
//        ResultSetMetaData rsmd = rs.getMetaData();//结果集元  
//          
//        System.out.println("下面这些方法是ResultSetMetaData中方法");  
//  
//        System.out.println("获得1列所在的Catalog名字 : " + rsmd.getCatalogName(1));  
//  
//        System.out.println("获得1列对应数据类型的类 " + rsmd.getColumnClassName(1));  
//  
//        System.out.println("获得该ResultSet所有列的数目 " + rsmd.getColumnCount());  
//  
//        System.out.println("1列在数据库中类型的最大字符个数" + rsmd.getColumnDisplaySize(1));  
//  
//        System.out.println(" 1列的默认的列的标题" + rsmd.getColumnLabel(1));  
//  
//        //System.out.println("1列的模式" + rsmd.GetSchemaName(1));  
//  
//        System.out.println("1列的类型,返回SqlType中的编号 " + rsmd.getColumnType(1));  
//  
//        System.out.println("1列在数据库中的类型，返回类型全名" + rsmd.getColumnTypeName(1));  
//  
//        System.out.println("1列类型的精确度(类型的长度): " + rsmd.getPrecision(1));  
//  
//        System.out.println("1列小数点后的位数 " + rsmd.getScale(1));  
//  
//        System.out.println("1列对应的模式的名称（应该用于Oracle） " + rsmd.getSchemaName(1));  
//  
//        System.out.println("1列对应的表名 " + rsmd.getTableName(1));  
//  
//        System.out.println("1列是否自动递增" + rsmd.isAutoIncrement(1));  
//  
//        System.out.println("1列在数据库中是否为货币型" + rsmd.isCurrency(1));  
//  
//        System.out.println("1列是否为空" + rsmd.isNullable(1));  
//  
//        System.out.println("1列是否为只读" + rsmd.isReadOnly(1));  
//  
//        System.out.println("1列能否出现在where中" + rsmd.isSearchable(1));  
//  
//  
//        rs.close();  
//          
//        pst.close();  
//          
//    }  
}
